package com.face.sql.jsu;

import java.io.Closeable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.IdentityHashMap;
import java.util.List;
import java.util.Set;

import com.face.bean.jsu.LostPopulationMessage;
import com.face.bean.jsu.UsernameAndName;

public class MessageDao {

	Connection con;
	ResultSet  rs;
	PreparedStatement pstmt;

	public void save(String username, String name, int sex, int age,
			double stature, double weight, String address, String location,
			Date time, String phone, String others) {
		
		String sql = "insert into lost_message(username,name,sex,age,stature,weight,"+
		             "address,location,time,phone,others) values(?,?,?,?,?,?,?,?,?,?,?)";
		try{
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			pstmt.setInt(3, sex);
			pstmt.setInt(4, age);
			pstmt.setDouble(5, stature);
			pstmt.setDouble(6, weight);
			pstmt.setString(7, address);
			pstmt.setString(8, location);
			pstmt.setDate(9, time);
			pstmt.setString(10, phone);
			pstmt.setString(11, others);
			
			pstmt.execute();
		}catch(SQLException e){
			
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	
	//根据id更新寻人启事信息
	public void updateById(int id, String name, String address, int age,
			String location, String others, String phone, int sex,
			double stature, Date date,double weight) {
		
		String sql = "update lost_message set name=?,sex=?,age=?,stature=?,weight=?,"+
	             "address=?,location=?,time=?,phone=?,others=? where id=?";
	try{
		con = JDBCUtil.getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, name);
		pstmt.setInt(2, sex);
		pstmt.setInt(3, age);
		pstmt.setDouble(4, stature);
		pstmt.setDouble(5, weight);
		pstmt.setString(6, address);
		pstmt.setString(7, location);
		pstmt.setDate(8, date);
		pstmt.setString(9, phone);
		pstmt.setString(10, others);
		pstmt.setInt(11, id);
		pstmt.executeUpdate();
	}catch(SQLException e){
		
	}finally{
		JDBCUtil.CloseConnection(rs, pstmt, con);
	}
	}
	
	public List<LostPopulationMessage> selectByUsername(String username) {
		List<LostPopulationMessage> lost = new ArrayList<>();
		String sql="select * from lost_message where username = ?";
		
		try {
			con = JDBCUtil.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, username);
			rs=pstmt.executeQuery();
			while(rs.next()){
				LostPopulationMessage message = new LostPopulationMessage();
				message.setId(rs.getInt("id"));
				message.setAddress(rs.getString("address"));
				message.setAge(rs.getInt("age"));
				message.setLocation(rs.getString("location"));
				message.setName(rs.getString("name"));
				message.setPhone(rs.getString("phone"));
				message.setOthers(rs.getString("others"));
				message.setSex(rs.getInt("sex"));
				message.setStature(rs.getDouble("stature"));
				message.setTime(rs.getString("time"));
				message.setUsername(rs.getString("username"));
				message.setWeight(rs.getDouble("weight"));
				lost.add(message);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
		return lost;
	}
	public List<LostPopulationMessage> selectByUsername(Set<String> keySet) {
		List<LostPopulationMessage> lost = new ArrayList<>();
		String sql="select * from lost_message where username in (";
		List<String> list = new ArrayList<>(keySet);
		for(int i=0;i<list.size();i++){
			sql+="'"+list.get(i)+"'";
			if(i==list.size()-1){
				sql+=")";
			}else{
				sql+=",";
			}
		}
		try {
			con = JDBCUtil.getConnection();
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				LostPopulationMessage message = new LostPopulationMessage();
				message.setAddress(rs.getString("address"));
				message.setAge(rs.getInt("age"));
				message.setLocation(rs.getString("location"));
				message.setName(rs.getString("name"));
				message.setPhone(rs.getString("phone"));
				message.setOthers(rs.getString("others"));
				message.setSex(rs.getInt("sex"));
				message.setStature(rs.getDouble("stature"));
				message.setTime(rs.getString("time"));
				message.setUsername(rs.getString("username"));
				message.setWeight(rs.getDouble("weight"));
				lost.add(message);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
		return lost;
		
	}

	public LostPopulationMessage selectByUsernameAndName(
			String username,String name) {
		String sql = "select * from lost_message where username=? and name=?";
		LostPopulationMessage message = new LostPopulationMessage();
		try {
			con = JDBCUtil.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			rs=pstmt.executeQuery();
			while(rs.next()){
				
				message.setAddress(rs.getString("address"));
				message.setAge(rs.getInt("age"));
				message.setLocation(rs.getString("location"));
				message.setName(rs.getString("name"));
				message.setPhone(rs.getString("phone"));
				message.setOthers(rs.getString("others"));
				message.setSex(rs.getInt("sex"));
				message.setStature(rs.getDouble("stature"));
				message.setTime(rs.getString("time"));
				message.setUsername(rs.getString("username"));
				message.setWeight(rs.getDouble("weight"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return message;
	}

	public void deleteByUsernameAndName(String username, String name) {
		String sql = "delete from lost_message where username = ? and name=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功删除"+rtn+"条记录");
		} catch (SQLException e) {
			System.out.println("删除失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}

	public UsernameAndName selectNameById(int id) {
		UsernameAndName name = new UsernameAndName();
		String sql = "select username,name from lost_message where id=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				name.setName(rs.getString("name"));
				name.setUsername(rs.getString("username"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return name;
	}
	
}
